﻿using System;
using System.IO;
using SpreadsheetLight;
using System.Collections.Generic;
using System.Diagnostics;

namespace ExcelUIMap
{
	/// <summary>
	/// Loads the UI Map from Excel
	/// </summary>
	internal class ExcelLoader
	{
		
		private const string cMapSheet = "Map";
		
		/// <summary>
		/// Loads a UI Map from the "Map" sheet of excel document
		/// </summary>
		internal static UIMapItemCollection LoadUIMap(string filename, Version version)
		{
			//setup
			if (!File.Exists(filename)) throw new FileNotFoundException(String.Format("Excel file not found {0}", filename));
			if (version==null) throw new Exception("No version specified");
			Trace.WriteLine(String.Format("Loading UI Map from file {0} for version {1}",Path.GetFileName(filename),version.ToString()));
			if (!DoesSheetExist(filename,cMapSheet)) throw new FileLoadException("Excel file doesnt contain map sheet");
			
			//open document
			SLDocument mapsheet = new SLDocument(filename,cMapSheet);
			//check version available
			if (!DoesVersionExist(mapsheet,version)) throw new FileLoadException("Version not found in map sheet");
			//get column index
			int versionindex = GetVersionColumnIndex(mapsheet,version);
			Trace.WriteLine(String.Format("Loading map data from excel column {0}",versionindex));
			
			//load data
			var result = new UIMapItemCollection();
			try
			{
				//get sheet dimensions
				var stats = mapsheet.GetWorksheetStatistics();
				var firstrow = stats.StartRowIndex;
				var lastrow = stats.EndRowIndex;
				var firstcolumn = stats.StartColumnIndex;
				
				//load sheet to collection
				for (int rowcounter = firstrow+1; rowcounter <= lastrow; rowcounter++)
				{
					var item = new UIMapItem();
					//get item details
					item.Name = mapsheet.GetCellValueAsString(rowcounter,firstcolumn);
					item.Context = mapsheet.GetCellValueAsString(rowcounter,firstcolumn+1);
					item.Description = mapsheet.GetCellValueAsString(rowcounter,firstcolumn+2);
					item.Value = mapsheet.GetCellValueAsString(rowcounter,versionindex);
					result.Add(item);
				}
			}
			finally
			{
				mapsheet.Dispose();
			}

			//finish
			Trace.WriteLine(String.Format("{0} Map items loaded",result.Count));
			return result;
		}
		
		
		/// <summary>
		/// Gets index of column with matching version
		/// </summary>
		private static int GetVersionColumnIndex(SLDocument sheet, Version version)
		{
			var index = -1;
			var stats = sheet.GetWorksheetStatistics();
			int firstrow = stats.StartRowIndex;
			int firstcolumn = stats.StartColumnIndex;
			int lastcolumn = stats.EndColumnIndex;
			for (int colcounter = firstcolumn; colcounter <= lastcolumn; colcounter++)
			{
				try
				{
					var colversion = new Version(sheet.GetCellValueAsString(firstrow,colcounter));
					if (colversion.Equals(version))
					{
						index = colcounter;
						break;
					}
						
				}
				catch { }
			}
			if (index==-1) throw new Exception("No index found for specified version");
			return index;
		}
		
		
		
		/// <summary>
		/// Checks if specified version is available in the sheet
		/// </summary>
		private static bool DoesVersionExist(SLDocument sheet, Version version)
		{
			var versions = GetSheetVersions(sheet);
			return versions.Contains(version);
		}
		
		
		/// <summary>
		/// Gets sorted list of available versions from first row in sheet
		/// </summary>
		private static List<Version> GetSheetVersions(SLDocument sheet)
		{
			var versions = new List<Version>();
			var stats = sheet.GetWorksheetStatistics();
			int firstrow = stats.StartRowIndex;
			int firstcolumn = stats.StartColumnIndex;
			int lastcolumn = stats.EndColumnIndex;
			for (int colcounter = firstcolumn; colcounter <= lastcolumn; colcounter++)
			{
				try
				{
					var version = new Version(sheet.GetCellValueAsString(firstrow,colcounter));
					versions.Add(version);
				}
				catch { }
			}
			versions.Sort();
			return versions;
		}
		
		
		/// <summary>
		/// Checks if sheet exists in document
		/// </summary>
		private static bool DoesSheetExist(string filename, string sheetname)
		{
			if (!File.Exists(filename)) throw new FileNotFoundException(String.Format("Excel file not found {0}", filename));
			SLDocument exceldoc = new SLDocument(filename);
			var sheets = exceldoc.GetSheetNames();
			exceldoc.Dispose();
			return sheets.Contains(sheetname);
		}
		
		
		
	}
}
